SQL Basics – Part 1

If you google “benefits of learning SQL”, you will be bemused by the so many articles written with titles “top 10 reasons, top 3 reasons, and top X reasons”. Don’t be confused wondering whether you need to memorize or be thorough with all those benefits to even get started.

With some intro done aside, I want to provide some help with a few SQL commands that you need to know on a day-to-day basis. That is if you want to use SQL, these are some of the most basic commands you will need to use.

You are better off knowing this - SQL is language designed to query databases and if you care to know what SQL stands for – it is Structured Query Language.

Most of the users do not use CREATE, UPDATE or DELETE TABLE commands in our day-to-day jobs for these commands are to do with making changes to the databases. More often than not you will see users accessing databases only to READ stuff in the databases. The C,R and U short for Create, Update and Delete, respectively are privileged accesses to database administrators, whose primary role and often a full time job is to manage databases.

For that reason, we will focus mostly on SELECT command that helps you fetch data from the data tables, to eventually draw some meaningful insights.

Running blind SELECT command on your tables can be very expensive, so we need to learn some efficient ways of fetching data and most importantly, fetching only the data that we need to analyze.

Let's dive into it:

SEELCT & FROM

This the most basic SQL statement to understand and use extensively.

The keyword SELECT determines which columns of a table to display. The keyword ‘FROM’ determines what table to retrieve data from.

For example, imagine there is a table called "houseplants" - a simple table containing plant information.

SELECT *

FROM houseplants

This query will pull up entire data table.

data tables for joining in SQL

Since it’s only 4 records in this example, the cost of running this query is low. But imagine a huge database of numerous plant varieties. Learning basic SQL skills can help retrieve data faster and efficiently. For those who are absolute beginners, a record is a row in the table.

We can also rename columns for better readability as we run this query. Keyword ‘as’ allows us to rename column names by creating aliases. An aliase is the new name we want to use for a column. Because a lot of times the source data will have whatever nams the creator kept for the dataset, it is common for the column names to be changed while extracting information from databases.

SELECT *

ID as ID,

Name as houseplant_name,

Sunlight as light_requirements

FROM houseplants;

This query will result in the table below. See how the columns names have changed now.

data tables for joining in SQL

WHERE

The WHERE keyword helps ‘filter’ data from the dataset.

In the above example, if we only want to view records of plants that need Bright and Direct light, we can modify the query as below:

SELECT

ID as ID,

Name as houseplant_name,

Sunlight as light_requirements

FROM houseplants h

WHERE h.light_requirements in (‘Bright Direct’);

In the above query we pass specific parameters in (‘Bright Direct’) to pull in records which meet the criteria. Here the syntax is to read a varchar, hence we use ‘in’ keyword followed by parentheses (‘ ‘)

WHERE command example

Another way to write this query would be:

SELECT

ID as ID,

Name as houseplant_name,

Sunlight as light_requirements

FROM houseplants h

WHERE h.ID = 3;

WHERE command example

We are using this example to demonstrate how to write ‘where’ clause with integer filter criteria. In an ideal scenario, knowing the ID of a record beforehand may not be possible.

ORDER BY

ORDER BY is SQL’s way of sorting the items retrieved from table. To comprehend this, think of excel sort - it works the same way, where we can pick one or more columns to sort on, and also define a hierarchy of art criteria. For example, sort the data first on column x, and then next on column b etc.

SELECT

ID as ID,

Name as houseplant_name,

Sunlight as light_requirements

FROM houseplants h

WHERE h.light_requirements in (‘Bright Indirect’);

Order by 1;

In this query we want to pull all the plant varieties with sunlight requirements as Bright and Indirect light, and sort them with ID (which is the 1st column in the records fetched).

If we want to explicitly mention column name, we can do that by qualifying the column name too. By default, SQL uses ascending sort (‘ASC’ keyword), when we don’t pass any arguments.

SELECT

ID as ID,

Name as houseplant_name,

Sunlight as light_requirements

FROM houseplants h

WHERE h.light_requirements in (‘Bright Indirect’);

Order by ID;

This query will result in the output below.

WHERE command example

SELECT

ID as ID,

Name as houseplant_name,

Sunlight as light_requirements

FROM houseplants h

WHERE h.light_requirements in (‘Bright Indirect’);

Order by desc;

This query will result in the output below.

WHERE command example

Drop me a line at [email protected] if you would like to have a quick discussion or need any help getting started with SQL.


If you are looking for a way to get started in the field of Analytics, Business Intelligence or RPA, feel free to drop us a line at [email protected]. We will be able to offer some advice or point to resources that can.


About Author

Nanda Ponnambalam
Nanda is the founder and Chief Trainer at Nxtgig.ai About us .